<?php
class Cms_Model_DAO_Article
{    
	private $_db;
	private static $_instance = null;
 
	/**
	 * @return Cms_Model_DAO_Article
	 * */
	public static function factory() {
		if(self::$_instance == null) {
			self::$_instance = new self();
		}
		
		return self::$_instance;
	}
	
    private function __construct()
    {
    	$module = Utils_Global::get('module');
		$this->_db = Utils_Global::getDbInstance($module);
    }
	
    /**
     * Get list of articles by given options
     * @param array $options
     * @return array
     * */
	public function getArticles($options = array()) {
		$sql = "SELECT `a`.`id`,	`a`.`image`, `a`.`category`,  `a`.`hash_folder`, `a`.`last_update`, 
		            `t`.`seo_title` `title_seo`, `t`.`content` `title`, `t2`.`content` `short_description` ";
		$from = " FROM `article` a INNER JOIN `translation` t ON `a`.`i18n_title` = `t`.`i18n_id` ";
		$from2 = " INNER JOIN `translation` t2 ON `a`.`i18n_short_description` = `t2`.`i18n_id` ";
		$where = " WHERE `a`.`active` = 1 ";
		
		if(is_array($options)) {
		    if($options['lang']) {
		        $where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} AND `t2`.`lang` = {$this->_db->quote($options['lang'])} ";
		    }
		    if($options['category']) {
		        $where .= " AND `a`.`category` = {$this->_db->quote($options['category'], 'INTEGER')} ";
		    }
		    
		    if($options['content']) {
		    	$where .= " AND `t`.`content` LIKE '%" . $options['content'] . "%' ";
		    }
		    if(is_array($options['exclude_cate']) && count($options['exclude_cate']) > 0) {
		        $where .= " AND `a`.`category` NOT IN(" . implode(',', $options['exclude_cate']). ") ";
		    }
		}
		
		$order = " ORDER BY `a`.`dateline` DESC ";
		if($options['order'] && $options['by']) {
			$order = " ORDER BY `a`.`{$options['order']}` {$options['by']} ";
		}
		
		$limit = "";
		
		if(isset($options['offset']) && $options['offset'] >=0 && $options['limit'] >0) {
			$limit = " limit {$this->_db->quote($options['offset'], 'INTEGER')}, {$this->_db->quote($options['limit'], 'INTEGER')} "; 
		}
// 		prBinh( $sql . $from . $from2 . $where . $order . $limit );
		$result = $this->_db->fetchAll ( $sql . $from . $from2 . $where . $order . $limit );
		return $result;
	}
	
	/**
     * Get list of articles by given options
     * @param array $options
     * @return array
     * */
	public function getWorkshops($options = array()) {
	    $sql = "SELECT `a`.`id`, `a`.`image`, `a`.`category`, `a`.`hash_folder`, `a`.`last_update`,
	                     `w`.`location`, `w`.`workshop_date`,
		            `t`.`seo_title` `title_seo`, `t`.`content` `title` ";
	    $from = " FROM `article` a INNER JOIN `translation` t ON `a`.`i18n_title` = `t`.`i18n_id` ";
	    $from2 = " INNER JOIN `workshop` `w` ON `a`.`id` = `w`.`article_id` ";
	    $where = " WHERE `a`.`active` = 1 ";
	    
	    if(is_array($options)) {
	    	if($options['lang']) {
	    		$where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} ";
	    	}
	    	if($options['category']) {
	    		$where .= " AND `a`.`category` = {$this->_db->quote($options['category'], 'INTEGER')} ";
	    	}
	    	if($options['location']) {
	    	    $where .= " AND `w`.`location` LIKE '%{$options['location']}%' ";
	    	}
	    	if($options['id']) {
	    	    $where .= " AND `a`.`id` = {$this->_db->quote($options['id'], 'INTEGER')} ";
	    	}
	    }
	    
	    $order = " ORDER BY `w`.`workshop_date` DESC ";
	    if($options['order'] && $options['by']) {
	    	$order = " ORDER BY `w`.`{$options['order']}` {$options['by']} ";
	    }
	    
	    $limit = "";
	    
	    if(isset($options['offset']) && $options['offset'] >=0 && $options['limit'] >0) {
	    	$limit = " limit {$this->_db->quote($options['offset'], 'INTEGER')}, {$this->_db->quote($options['limit'], 'INTEGER')} ";
	    }
	    $result = $this->_db->fetchAll ( $sql . $from . $from2 . $where . $order . $limit );
	    return $result;
	}
	
	public function getDetailArticle($options = array()) {
	    $sql = "SELECT `a`.`id`, `a`.`last_update`, `a`.`category`, `t`.`seo_title` `title_seo`, `t`.`content` `title`, `t2`.`content` `content`,`t`.`lang`  ";
	    $from = " FROM `article` a INNER JOIN `translation` t ON `a`.`i18n_title` = `t`.`i18n_id` ";
	    $from2 = " INNER JOIN `translation` t2 ON `a`.`i18n_content` = `t2`.`i18n_id` ";
	    $where = " WHERE `a`.`active` = 1 ";
	    if(is_array($options)) {
	    	if($options['lang']) {
	    		$where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} AND `t2`.`lang` = {$this->_db->quote($options['lang'])} ";
	    		$where .= " AND `t3`.`lang` = {$this->_db->quote($options['lang'])} ";
	    		$where .= " AND `t4`.`lang` = {$this->_db->quote($options['lang'])} ";
	    		$where .= " AND `t5`.`lang` = {$this->_db->quote($options['lang'])} ";
	    	}
	    	if($options['id']) {
	    		$where .= " AND `a`.`id` = {$this->_db->quote($options['id'], 'INTEGER')} ";
	    	}
	    }
	    
	    $order = " ORDER BY `a`.`last_update` DESC ";
	    if($options['order'] && $options['by']) {
	    	$order = " ORDER BY `a`.`{$options['order']}` {$options['by']} ";
	    }
	    
	    //For SEO
	    $sql .= ", `t3`.`content` `seo_title_content`, `t4`.`content` `seo_description_content`, `t5`.`content` `seo_keyword_content` ";
	    $from .= " INNER JOIN `translation` `t3` ON `a`.`i18n_seo_title` = `t3`.`i18n_id`
		                INNER JOIN `translation` `t4` ON `a`.`i18n_seo_description` = `t4`.`i18n_id`
		                INNER JOIN `translation` `t5` ON `a`.`i18n_seo_keywords` = `t5`.`i18n_id` ";
	    
	    $limit = "";
	    
	    if(isset($options['offset']) && $options['offset'] >=0 && $options['limit'] >0) {
	    	$limit = " limit {$this->_db->quote($options['offset'], 'INTEGER')}, {$this->_db->quote($options['limit'], 'INTEGER')} ";
	    }
	    //prBinh($sql . $from . $where . $order . $limit );
	    $result = $this->_db->fetchAll ( $sql . $from . $from2 . $where . $order . $limit );
	    return $result;
	}
	
	/**
	 * Get article count by given options
	 * @param array $options
	 * @return integer
	 * */
	public function getArticleCount($options = array()) {
		$sql = "SELECT count(*) as `count` ";
		$from = " FROM `article` a INNER JOIN `translation` t ON `a`.`i18n_title` = `t`.`i18n_id` ";
		$from2 = " INNER JOIN `translation` t2 ON `a`.`i18n_short_description` = `t2`.`i18n_id` ";
		$where = " WHERE active = 1 ";
	    if(is_array($options)) {
		    if($options['lang']) {
		        $where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} AND `t2`.`lang` = {$this->_db->quote($options['lang'])} ";
		    }
		    if($options['category']) {
		        $where .= " AND `a`.`category` = {$this->_db->quote($options['category'], 'INTEGER')} ";
		    }
		    
		    if($options['content']) {
		    	$where .= " AND `t`.`content` LIKE '%" . $options['content'] . "%' ";
		    }
		    if(is_array($options['exclude_cate']) && count($options['exclude_cate']) > 0) {
		    	$where .= " AND `a`.`category` NOT IN(" . implode(',', $options['exclude_cate']). ") ";
		    }
		}
		
		$result = $this->_db->fetchOne($sql . $from . $from2 . $where);
		return $result;
	}
	
	/**
	 * Update article with data by given id
	 * @param integer $id
	 * @param array $data
	 * @return 1 on success, 0 on failure
	 * */
	public function update($id, $data) {
	    return $this->_db->update('article', $data, "id = {$this->_db->quote($id, 'INTEGER')}");
	}
	
	/**
	 * +1 view count for given id
	 * @param integer $id
	 * @return 1 on success, 0 on failure
	 * */
	public function addViewCount($id) {
	    $sql = "UPDATE `article` SET `view_count` = `view_count` + 1 ";
	    $where = " WHERE `id` = {$this->_db->quote($id, 'INTEGER')} ";
	    $result = $this->_db->query($sql . $where);
	    return $result;
	}
}